-- ----------------------------
-- 博客系统数据库
-- ----------------------------
CREATE DATABASE IF NOT EXISTS `blog_system` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE `blog_system`;

-- ----------------------------
-- 用户表 - 存储用户基本信息和认证信息
-- ----------------------------
CREATE TABLE `boke_user` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '用户ID，主键',
  `username` varchar(50) NOT NULL COMMENT '用户名，唯一标识',
  `password_hash` varchar(100) NOT NULL COMMENT '加密后的密码',
  `email` varchar(100) DEFAULT NULL COMMENT '邮箱地址',
  `avatar_url` varchar(255) DEFAULT NULL COMMENT '头像URL',
  `bio` text COMMENT '个人简介',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '注册时间',
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_username` (`username`),
  KEY `idx_email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户表';

-- ----------------------------
-- 用户安全信息表 - 存储敏感的安全相关信息
-- ----------------------------
CREATE TABLE `boke_user_security` (
  `user_id` bigint(20) UNSIGNED NOT NULL COMMENT '关联的用户ID',
  `password_salt` varchar(50) NOT NULL COMMENT '密码盐值',
  `last_login_ip` varchar(45) DEFAULT NULL COMMENT '最后登录IP',
  `last_login_time` datetime DEFAULT NULL COMMENT '最后登录时间',
  `login_attempts` tinyint(3) UNSIGNED NOT NULL DEFAULT '0' COMMENT '登录失败次数',
  `locked_until` datetime DEFAULT NULL COMMENT '账号锁定截止时间',
  `two_factor_secret` varchar(100) DEFAULT NULL COMMENT '双因素认证密钥',
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户安全信息表';

-- ----------------------------
-- 文章表 - 存储博客文章内容
-- ----------------------------
CREATE TABLE `boke_article` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '文章ID，主键',
  `user_id` bigint(20) UNSIGNED NOT NULL COMMENT '作者ID',
  `title` varchar(200) NOT NULL COMMENT '文章标题',
  `content` longtext NOT NULL COMMENT '文章内容（富文本）',
  `summary` text COMMENT '文章摘要',
  `status` tinyint(3) UNSIGNED NOT NULL DEFAULT '0' COMMENT '状态：0-草稿，1-已发布，2-已删除',
  `views` bigint(20) UNSIGNED NOT NULL DEFAULT '0' COMMENT '阅读量',
  `likes` bigint(20) UNSIGNED NOT NULL DEFAULT '0' COMMENT '点赞数',
  `comments_count` bigint(20) UNSIGNED NOT NULL DEFAULT '0' COMMENT '评论数',
  `is_hot` tinyint(1) UNSIGNED NOT NULL DEFAULT '0' COMMENT '是否热门文章',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `published_at` datetime DEFAULT NULL COMMENT '发布时间',
  PRIMARY KEY (`id`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_status` (`status`),
  KEY `idx_created_at` (`created_at`),
  KEY `idx_views` (`views`),
  KEY `idx_is_hot` (`is_hot`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='文章表';

-- ----------------------------
-- 文章分类表 - 管理文章的分类
-- ----------------------------
CREATE TABLE `boke_article_category` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '分类ID，主键',
  `name` varchar(50) NOT NULL COMMENT '分类名称',
  `parent_id` bigint(20) UNSIGNED DEFAULT NULL COMMENT '父分类ID，支持多级分类',
  `sort_order` int(11) NOT NULL DEFAULT '0' COMMENT '排序顺序',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_name_parent_id` (`name`, `parent_id`),
  KEY `idx_parent_id` (`parent_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='文章分类表';

-- ----------------------------
-- 文章-分类关联表 - 多对多关系
-- ----------------------------
CREATE TABLE `boke_article_category_relation` (
  `article_id` bigint(20) UNSIGNED NOT NULL COMMENT '文章ID',
  `category_id` bigint(20) UNSIGNED NOT NULL COMMENT '分类ID',
  PRIMARY KEY (`article_id`, `category_id`),
  KEY `idx_category_id` (`category_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='文章-分类关联表';

-- ----------------------------
-- 文章标签表 - 管理文章的标签
-- ----------------------------
CREATE TABLE `boke_article_tag` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '标签ID，主键',
  `name` varchar(50) NOT NULL COMMENT '标签名称',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='文章标签表';

-- ----------------------------
-- 文章-标签关联表 - 多对多关系
-- ----------------------------
CREATE TABLE `boke_article_tag_relation` (
  `article_id` bigint(20) UNSIGNED NOT NULL COMMENT '文章ID',
  `tag_id` bigint(20) UNSIGNED NOT NULL COMMENT '标签ID',
  PRIMARY KEY (`article_id`, `tag_id`),
  KEY `idx_tag_id` (`tag_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='文章-标签关联表';

-- ----------------------------
-- 文章点赞记录表
-- ----------------------------
CREATE TABLE `boke_article_like` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '记录ID，主键',
  `user_id` bigint(20) UNSIGNED NOT NULL COMMENT '用户ID',
  `article_id` bigint(20) UNSIGNED NOT NULL COMMENT '文章ID',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '点赞时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_user_article` (`user_id`, `article_id`),
  KEY `idx_article_id` (`article_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='文章点赞记录表';

-- ----------------------------
-- 文章评论表
-- ----------------------------
CREATE TABLE `boke_article_comment` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '评论ID，主键',
  `user_id` bigint(20) UNSIGNED NOT NULL COMMENT '评论用户ID',
  `article_id` bigint(20) UNSIGNED NOT NULL COMMENT '文章ID',
  `parent_id` bigint(20) UNSIGNED DEFAULT NULL COMMENT '父评论ID，支持评论嵌套',
  `content` text NOT NULL COMMENT '评论内容',
  `status` tinyint(3) UNSIGNED NOT NULL DEFAULT '1' COMMENT '状态：0-待审核，1-已通过，2-已拒绝',
  `likes` bigint(20) UNSIGNED NOT NULL DEFAULT '0' COMMENT '点赞数',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`id`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_article_id` (`article_id`),
  KEY `idx_parent_id` (`parent_id`),
  KEY `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='文章评论表';

-- ----------------------------
-- 系统配置表 - 存储博客全局配置
-- ----------------------------
CREATE TABLE `boke_system_config` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '配置ID，主键',
  `key` varchar(50) NOT NULL COMMENT '配置键',
  `value` text NOT NULL COMMENT '配置值',
  `description` varchar(255) DEFAULT NULL COMMENT '配置描述',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_key` (`key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='系统配置表';

-- 插入一些初始配置
INSERT INTO `boke_system_config` (`key`, `value`, `description`) VALUES
('site_name', 'BlogHub', '网站名称'),
('site_description', '分享你的创作灵感', '网站描述'),
('posts_per_page', '10', '每页显示的文章数量'),
('is_comment_moderated', '1', '评论是否需要审核（0-不需要，1-需要）');

-- ----------------------------
-- 模拟数据 - 用户表
-- ----------------------------
INSERT INTO `boke_user` (`username`, `password_hash`, `email`, `avatar_url`, `bio`, `created_at`) VALUES
('admin', '$2a$10$Z6pM7GjRw8vB7lB8qHdHd.5Z6jBzB7dZ7dZ7dZ7dZ7dZ7dZ7dZ7', 'admin@example.com', 'https://picsum.photos/200/200?random=1', '博客管理员', '2023-01-01 10:00:00'),
('user1', '$2a$10$Z6pM7GjRw8vB7lB8qHdHd.5Z6jBzB7dZ7dZ7dZ7dZ7dZ7dZ7dZ7', 'user1@example.com', 'https://picsum.photos/200/200?random=2', '技术爱好者', '2023-01-02 11:30:00'),
('user2', '$2a$10$Z6pM7GjRw8vB7lB8qHdHd.5Z6jBzB7dZ7dZ7dZ7dZ7dZ7dZ7dZ7', 'user2@example.com', 'https://picsum.photos/200/200?random=3', '生活博主', '2023-01-03 14:15:00');

-- ----------------------------
-- 模拟数据 - 用户安全信息表
-- ----------------------------
INSERT INTO `boke_user_security` (`user_id`, `password_salt`, `last_login_ip`, `last_login_time`) VALUES
(1, 'salt_admin', '127.0.0.1', '2023-07-01 09:30:00'),
(2, 'salt_user1', '192.168.1.100', '2023-07-01 08:45:00'),
(3, 'salt_user2', '192.168.1.101', '2023-07-01 10:15:00');

-- ----------------------------
-- 模拟数据 - 文章分类表
-- ----------------------------
INSERT INTO `boke_article_category` (`name`, `parent_id`, `sort_order`) VALUES
('技术', NULL, 1),
('生活', NULL, 2),
('前端开发', 1, 1),
('后端开发', 1, 2),
('日常分享', 2, 1),
('旅行见闻', 2, 2);

-- ----------------------------
-- 模拟数据 - 文章标签表
-- ----------------------------
INSERT INTO `boke_article_tag` (`name`) VALUES
('JavaScript'),
('React'),
('Vue'),
('Node.js'),
('旅行'),
('美食'),
('摄影');

-- ----------------------------
-- 模拟数据 - 文章表
-- ----------------------------
INSERT INTO `boke_article` (`user_id`, `title`, `content`, `summary`, `status`, `views`, `likes`, `comments_count`, `is_hot`, `created_at`, `published_at`) VALUES
(2, 'JavaScript高级特性详解', '<p>本文详细介绍了JavaScript的高级特性...</p>', '探索JavaScript的高级特性和应用场景', 1, 1254, 45, 12, 1, '2023-03-15 09:00:00', '2023-03-15 09:00:00'),
(2, 'React Hooks最佳实践', '<p>React Hooks是React 16.8版本引入的新特性...</p>', 'React Hooks的使用技巧和最佳实践', 1, 876, 32, 8, 1, '2023-04-20 14:30:00', '2023-04-20 14:30:00'),
(3, '我的云南旅行日记', '<p>上个月去了云南旅行，分享一下我的行程...</p>', '云南旅行的美好回忆和攻略', 1, 2345, 78, 23, 1, '2023-05-10 11:15:00', '2023-05-10 11:15:00'),
(2, 'Vue3组合式API入门', '<p>Vue3的组合式API带来了更灵活的代码组织方式...</p>', 'Vue3组合式API的基本用法和优势', 1, 654, 23, 5, 0, '2023-06-05 16:45:00', '2023-06-05 16:45:00'),
(3, '周末美食分享', '<p>周末在家尝试做了几道新菜，分享一下食谱...</p>', '简单又美味的家常菜做法', 1, 987, 35, 9, 0, '2023-06-18 10:30:00', '2023-06-18 10:30:00'),
(2, 'Node.js性能优化', '<p>本文介绍了几种Node.js应用的性能优化方法...</p>', '提升Node.js应用性能的实用技巧', 0, 0, 0, 0, 0, '2023-07-01 14:20:00', NULL);

-- ----------------------------
-- 模拟数据 - 文章-分类关联表
-- ----------------------------
INSERT INTO `boke_article_category_relation` (`article_id`, `category_id`) VALUES
(1, 3),
(2, 3),
(3, 6),
(4, 3),
(5, 5),
(6, 4);

-- ----------------------------
-- 模拟数据 - 文章-标签关联表
-- ----------------------------
INSERT INTO `boke_article_tag_relation` (`article_id`, `tag_id`) VALUES
(1, 1),
(2, 1),
(2, 2),
(3, 5),
(3, 7),
(4, 1),
(4, 3),
(5, 6),
(6, 1),
(6, 4);

-- ----------------------------
-- 模拟数据 - 文章点赞记录表
-- ----------------------------
INSERT INTO `boke_article_like` (`user_id`, `article_id`, `created_at`) VALUES
(1, 1, '2023-03-16 10:20:00'),
(1, 2, '2023-04-21 11:15:00'),
(1, 3, '2023-05-11 09:45:00'),
(2, 3, '2023-05-12 14:30:00'),
(3, 1, '2023-03-18 16:20:00'),
(3, 2, '2023-04-25 10:10:00');

-- ----------------------------
-- 模拟数据 - 文章评论表
-- ----------------------------
INSERT INTO `boke_article_comment` (`user_id`, `article_id`, `parent_id`, `content`, `status`, `likes`, `created_at`) VALUES
(1, 1, NULL, '非常实用的文章，感谢分享！', 1, 5, '2023-03-17 10:30:00'),
(3, 1, 1, '请问作者，这些特性在旧版浏览器中支持如何？', 1, 2, '2023-03-18 14:20:00'),
(2, 1, 2, '大部分特性都有polyfill支持，现代浏览器基本都支持。', 1, 3, '2023-03-19 09:10:00'),
(2, 3, NULL, '云南是个好地方，我也很想去！', 1, 7, '2023-05-13 11:45:00'),
(1, 3, 4, '请问你去了哪些景点？行程安排了几天？', 1, 2, '2023-05-14 13:20:00'),
(3, 3, 5, '我去了大理、丽江和香格里拉，一共玩了10天。', 1, 4, '2023-05-15 10:05:00'),
(3, 2, NULL, 'React Hooks确实很强大，我也在项目中使用了。', 1, 3, '2023-04-27 15:30:00');